using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Security.Cryptography;
using System.Text;
using System.IO;
using System.Windows.Forms;
//using System.Data.SqlServerCe;
using BiSA;
/// <summary>
/// DataAccess Helper class for SQL SErver
/// </summary>
public class MKDataAccesser
{
	public MKDataAccesser()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static SqlCommand CreateCommand(String cmdText)
    {
        SqlConnection conn = GetConnection();
        SqlCommand com = new SqlCommand(cmdText, conn);
        com.CommandTimeout = 3600;
        com.CommandType = System.Data.CommandType.StoredProcedure;
        return com;
    }

    public static SqlCommand CreateCommand(String cmdText, CommandType commandType)
    {
        SqlConnection conn = GetConnection();
        SqlCommand com = new SqlCommand(cmdText, conn);
        com.CommandType = commandType;
        com.CommandTimeout = 3600;
        return com;
    }
    public static SqlConnection GetConnection()
    {
        string dbConnStr = BiSA.Properties.Settings.Default.BiSAConnectionString.ToString();
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = dbConnStr;
        return conn;
    }
    
    public static Hashtable ExecuteScalar(SqlCommand com)
    {
        try
        {
            SqlConnection conn = com.Connection;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
                
            }
            com.ExecuteScalar();
            if (com.Transaction == null)
            {
                conn.Close();
            }

            return DeriveOutputParams(com);
        }
        catch (Exception ex)
        {
            if (com.Transaction == null)
            {
                com.Connection.Close();
            }
            else
            {
                com.Transaction.Rollback();
                //com.Transaction.Dispose();
                com.Connection.Close();
            }
            throw ex;
        }
    }
    private static Hashtable DeriveOutputParams(SqlCommand com)
    {
        System.Collections.Hashtable outParams = new System.Collections.Hashtable();
        for (int i = 0; i < com.Parameters.Count; i++)
        {
            if (com.Parameters[i].Direction == System.Data.ParameterDirection.Output)
            {
                String paramName = com.Parameters[i].ParameterName;
                paramName = paramName.Replace("@", "");
                outParams.Add(paramName, com.Parameters[i].Value);
            }
        }
        return outParams;
    }
    public static DataSet GetRecord(SqlCommand com)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        ds.Tables.Add(dt);
        GetRecord(com, dt);
        return ds;
    }
    public static void GetRecord(SqlCommand com, DataSet ds, string dsTable)
    {
        GetRecord(com, ds.Tables[dsTable]);
    }

    public static void GetRecord(SqlCommand com, DataTable dt)
    {
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.TableMappings.Add("Table", dt.TableName);
            da.Fill(dt);
        }
        catch 
        {
            if (com.Transaction == null)
            {
                com.Connection.Close();
            }
            else
            {
                com.Transaction.Rollback();
                //com.Transaction.Dispose();
                com.Connection.Close();
            }
            throw;
        }
    }
    //
    // Summary:
    //     Executes a Transact-SQL statement against the connection and returns the
    //     number of rows affected.
    //
    // Returns:
    //     The number of rows affected.
    //
    // Exceptions:
    //   System.Data.SqlClient.SqlException:
    //     An exception occurred while executing the command against a locked row. This
    //     exception is not generated when you are using Microsoft .NET Framework version
    //     1.0.
    public static void ExecuteMyQuery(SqlCommand cmd) {
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
    }
}
